package com.sjq.yqgl.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.ArrayList;
import java.util.Map;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import com.sjq.yqgl.dao.YqmqjczDao;
import com.sjq.common.dao.GenericDao;
import com.sjq.common.util.PageResults;
import com.sjq.yqgl.entity.Yqmqjcz;

@Repository
public class YqmqjczDaoImpl extends GenericDao<Yqmqjcz> implements YqmqjczDao {

	 @Override
	 public PageResults<?> selectYqmqjczByPage(int pageNo, int pageSize,Yqmqjcz yqmqjcz){
	 	StringBuilder paramSql = new StringBuilder();
		List params = new ArrayList();
		//查询条件
		/*if (yqmqjcz.getXxzjbh() != null&& !(yqmqjcz.getXxzjbh().equals(""))) {
			paramSql.append(" and xxzjbh=? ");
			params.add(yqmqjcz.getXxzjbh().trim());
		}*/
		
		if (yqmqjcz.getXxzjbh() != null&& !(yqmqjcz.getXxzjbh().equals(""))) {
			paramSql.append(" and xxzjbh=? ");
			params.add(yqmqjcz.getXxzjbh().trim());
		}
		if (yqmqjcz.getGlrwid() != null&& !(yqmqjcz.getGlrwid().equals(""))) {
			paramSql.append(" and glrwid=? ");
			params.add(yqmqjcz.getGlrwid().trim());
		}
		if (yqmqjcz.getDjrjh() != null&& !(yqmqjcz.getDjrjh().equals(""))) {
			paramSql.append(" and instr(djrjh,?)>0 ");
			params.add(yqmqjcz.getDjrjh().trim());
		}
		
		if (yqmqjcz.getXm() != null&& !(yqmqjcz.getXm().equals(""))) {
			paramSql.append(" and instr(xm,?)>0 ");
			params.add(yqmqjcz.getXm().trim());
		}
		if (yqmqjcz.getStzk() != null&& !(yqmqjcz.getStzk().equals(""))) {
			paramSql.append(" and instr(stzk,?)>0 ");
			params.add(yqmqjcz.getStzk().trim());
		}
		if (yqmqjcz.getFhcs() != null&& !(yqmqjcz.getFhcs().equals(""))) {
			paramSql.append(" and instr(fhcs,?)>0 ");
			params.add(yqmqjcz.getFhcs().trim());
		}
		
		if (yqmqjcz.getSjhm() != null&& !(yqmqjcz.getSjhm().equals(""))) {
			paramSql.append(" and sjhm=? ");
			params.add(yqmqjcz.getSjhm().trim());
		}
		if (yqmqjcz.getTbsj_s() != null&& !(yqmqjcz.getTbsj_s().equals(""))) {
			paramSql.append(" and (JCSJ>?  or JCSJ=?)");
			params.add(yqmqjcz.getTbsj_s().trim());
			params.add(yqmqjcz.getTbsj_s().trim());
		}
		if (yqmqjcz.getTbsj_e() != null&& !(yqmqjcz.getTbsj_e().equals(""))) {
			paramSql.append(" and (JCSJ<?  or JCSJ=?) ");
			params.add(yqmqjcz.getTbsj_e().trim());
			params.add(yqmqjcz.getTbsj_e().trim());
		}
		
		
		
		String sql_fenye="select YHZGX,SFZS,STZK,FHCS,SQBBR,LXDH,SQMJ,SQMJDH,to_char(DJSJ,'yyyy-MM-dd hh24:mi:ss') as DJSJ,DJDW,DJDWMC,DJRJH,GLRWID,DJBGBS,XXZJBH,XM,XH,SFHM,HJDZ,XZZ,SJHM,JCSJ from T_YQ_MQJCZ where DJBGBS <>3 " + paramSql.toString() ;
		PageResults<Map<String, String>> results = new PageResults<Map<String, String>>();
		StringBuilder pageSql = new StringBuilder()
				.append("select * from ")
				.append(" (select tmp_1.*, rownum as rowindex from ("+sql_fenye+") tmp_1 ) tmp_2 ")
				.append(" where rowindex between ")
				.append(((pageNo-1)*pageSize + 1))
				.append(" and ")
				.append(((pageNo - 1) * pageSize + pageSize));
		List<Map<String, String>> list=new ArrayList<Map<String, String>>();
		try {
			list = this.getListBySql(pageSql.toString(),params.toArray());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		String sql="select count(1) as num from T_YQ_MQJCZ where DJBGBS <>3  "+paramSql.toString()+" ";
		SqlRowSet sqlRowSet = this.getJdbcTemplate().queryForRowSet(sql,params.toArray());
		int count = 0;
		while(sqlRowSet.next()){
			count = sqlRowSet.getInt("num");
		}
		results.setTotalCount(count);
        results.setPageNo(pageNo);
        results.setPageSize(pageSize);
		results.setResults(list);
		return results;	
	 }
	 @Override
	 public int addYqmqjcz(Yqmqjcz yqmqjcz){
	 	String sql="insert into T_YQ_MQJCZ ( yhzgx,sfzs,stzk,fhcs,sqbbr,lxdh,sqmj,sqmjdh,djsj,djdw,djdwmc,djrjh,glrwid,djbgbs,xxzjbh,xm,xh,sfhm,hjdz,xzz,sjhm,jcsj) values (?,?,?,?,?,?,?,?,to_date(?,'yyyy-MM-dd hh24:mi:ss'),?,?,?,?,?,?,?,?,?,?,?,?,?)  ";
	 	Object [] params={yqmqjcz.getYhzgx(),yqmqjcz.getSfzs(),yqmqjcz.getStzk(),yqmqjcz.getFhcs(),yqmqjcz.getSqbbr(),yqmqjcz.getLxdh(),yqmqjcz.getSqmj(),yqmqjcz.getSqmjdh(),yqmqjcz.getDjsj(),yqmqjcz.getDjdw(),yqmqjcz.getDjdwmc(),yqmqjcz.getDjrjh(),yqmqjcz.getGlrwid(),yqmqjcz.getDjbgbs(),yqmqjcz.getXxzjbh(),yqmqjcz.getXm(),yqmqjcz.getXh(),yqmqjcz.getSfhm(),yqmqjcz.getHjdz(),yqmqjcz.getXzz(),yqmqjcz.getSjhm(),yqmqjcz.getJcsj()};
	 	return this.getJdbcTemplate().update(sql.toString(),params);
	 }
	 
	 @Override
	 public int deleteYqmqjcz(String yqmqjczId){
		 String sql="update T_YQ_MQJCZ set djbgbs=3 where glrwid=?";
		 Object [] params={ yqmqjczId};
		 return this.getJdbcTemplate().update(sql.toString(),params);
	 }
	 
	  @Override
	 public int updateYqmqjcz(Yqmqjcz yqmqjcz){
	 	String sql="update T_YQ_MQJCZ set YHZGX=?,SFZS=?,STZK=?,FHCS=?,SQBBR=?,LXDH=?,SQMJ=?,SQMJDH=?,DJSJ=?,DJDW=?,DJDWMC=?,DJRJH=?,GLRWID=?,DJBGBS=?,XXZJBH=?,XM=?,XH=?,SFHM=?,HJDZ=?,XZZ=?,SJHM=?,JCSJ=?  where xxzjbh=?";
	 	Object [] params={yqmqjcz.getYhzgx(),yqmqjcz.getSfzs(),yqmqjcz.getStzk(),yqmqjcz.getFhcs(),yqmqjcz.getSqbbr(),yqmqjcz.getLxdh(),yqmqjcz.getSqmj(),yqmqjcz.getSqmjdh(),yqmqjcz.getDjsj(),yqmqjcz.getDjdw(),yqmqjcz.getDjdwmc(),yqmqjcz.getDjrjh(),yqmqjcz.getGlrwid(),yqmqjcz.getDjbgbs(),yqmqjcz.getXxzjbh(),yqmqjcz.getXm(),yqmqjcz.getXh(),yqmqjcz.getSfhm(),yqmqjcz.getHjdz(),yqmqjcz.getXzz(),yqmqjcz.getSjhm(),yqmqjcz.getJcsj()};
	 	return this.getJdbcTemplate().update(sql.toString(),params);
	 }
	 
	 @Override
	 public List<Map<String, String>> findYqmqjczByListAll(Yqmqjcz yqmqjcz){
		StringBuilder paramSql = new StringBuilder();
		List params = new ArrayList();
		//查询条件
		if (yqmqjcz.getXxzjbh() != null&& !(yqmqjcz.getXxzjbh().equals(""))) {
			paramSql.append(" and xxzjbh=? ");
			params.add(yqmqjcz.getXxzjbh().trim());
		}
		String sql="select YHZGX,SFZS,STZK,FHCS,SQBBR,LXDH,SQMJ,SQMJDH,to_char(DJSJ,'yyyy-MM-dd hh24:mi:ss') as DJSJ,DJDW,DJDWMC,DJRJH,GLRWID,DJBGBS,XXZJBH,XM,XH,SFHM,HJDZ,XZZ,SJHM,JCSJ from T_YQ_MQJCZ where DJBGBS <>3  "+ paramSql.toString() ;
		List<Map<String, String>> list=new ArrayList<Map<String, String>>();
		try {
			list = this.getListBySql(sql.toString(),params.toArray());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
}